from copy import copy

import openpyxl
import pandas as pd
from openpyxl.styles import Alignment, Side, Border, Font  # 设置表格样式
from openpyxl.utils import get_column_letter

font = Font(
    name="宋体",  # 字体
    size=10,  # 字体大小
    color="000000",  # 字体颜色，用16进制rgb表示
    bold=False,  # 是否加粗，True/False
    italic=False,  # 是否斜体，True/False
    strike=None,  # 是否使用删除线，True/False
    underline=None,  # 下划线, 可选'singleAccounting', 'double', 'single', 'doubleAccounting'
)
alignment = Alignment(
    horizontal='center',  # 水平对齐模式
    vertical='center',  # 垂直对齐模式
    text_rotation=None,  # 旋转角度
    wrap_text=None  # 是否自动换行
)
side = Side(style="thin", color="000000")
border = Border(left=side, right=side, top=side, bottom=side)


# 复制表头格式写入数据
def selfsheet_type(df, sheetname, wb, wb2):
    """
    复制原来的表头格式到新的 excel 文件并写入新的 excel 文件
    :param df: 该文件对应的人员类别
    :param sheetname: 该文件对应的 sheet 页
    :param wb: 该文件的 WorkBook
    :param wb2: 新的 WorkBook
    :return: None
    """
    '''
        https://www.shuzhiduo.com/A/6pdD9pxDzw/
    '''

    df = df.iloc[1:, :]

    # ========= 创建 sheet 页 ==========
    ws = None
    flag = False
    if sheetname.find('临时汇总') != -1:
        keyname = sheetname.split('临时汇总')[0] + '临时汇总'
        sheetname = sheetname.split('临时汇总')[-1]
        flag = True

    for sname in wb.sheetnames:
        if sname == sheetname:
            ws = wb[sname]
            if flag:
                ws2 = wb2.create_sheet(keyname)
            else:
                ws2 = wb2.create_sheet(sname)

    # ========= 复制表头(即前5行)的内容和格式 ==========
    max_row = 5  # 最大行数
    max_column = ws.max_column  # 最大列数
    if max_column > 60:
        max_column = 60
    wm = tuple(zip(ws.merged_cells))  # 开始处理合并单元格
    if len(wm) > 0:
        for i in range(0, len(wm)):
            cell2 = str(wm[i]).replace('(<MergedCellRange ', '').replace('>,)', '')
            print("MergeCell : %s" % cell2)
            # try:
            #     int(cell2.split(':')[0][-2:])
            #     continue
            # except:
            #     pass
            if int(cell2.split(':')[0][-1:]) > 5:
                continue
            ws2.merge_cells(cell2)
    for m in range(1, max_row + 1):
        ws2.row_dimensions[m].height = ws.row_dimensions[m].height
        for n in range(max_column):
            c = get_column_letter(n + 1)

            i = '%s%d' % (c, m)  # 单元格编号
            ws2[i].border = border
            if m == 1:
                ws2.column_dimensions[c].width = ws.column_dimensions[c].width
            try:
                getattr(ws[f'{c}{str(m)}'], "value")
                cell1 = ws[i]  # 获取data单元格数据

                ws2[i].value = cell1.value  # 值：赋值到ws2单元格
                ws2[i].comment = cell1.comment  # 批注：赋值到ws2单元格
                if cell1.has_style:  # 拷贝格式
                    ws2[i].font = copy(cell1.font)
                    # ws2[i].border = copy(cell1.border)
                    ws2[i].fill = copy(cell1.fill)
                    ws2[i].number_format = copy(cell1.number_format)
                    ws2[i].protection = copy(cell1.protection)
                    ws2[i].alignment = copy(cell1.alignment)
            except:
                continue

    # ========= 填充工资表对应人员数据 ==========
    for i in range(len(df)):
        ws2.row_dimensions[int(f'{6 + i}')].height = 30  # 设置行高
        for j in range(len(df.columns)):
            try:
                ws2[f'{get_column_letter(j + 1)}{6 + i}'].font = font  # 设置字体样式
                ws2[f'{get_column_letter(j + 1)}{6 + i}'].alignment = alignment  # 设置内容对齐格式
                ws2[f'{get_column_letter(j + 1)}{6 + i}'].border = border  # 设置边框样式
                if pd.isna(df.iloc[i, j]):
                    continue
                ws2[f'{get_column_letter(j + 1)}{6 + i}'] = df.iloc[i, j]

            except:
                continue
